# -*- coding: utf-8 -*-
"""
-------------------------------------------------
  Description :
  Author :    Administrator
  date：     2022/11/24
-------------------------------------------------
  Change Activity:
          2022/11/24:
-------------------------------------------------
"""

import pymysql
from dbutils.pooled_db import PooledDB

from loguru import logger
from pymysql.err import IntegrityError

MYSQL_HOST = '192.168.44.20'
MYSQL_USER = 'root'
MYSQL_PW = '%qq123456..'
MYSQL_DB = 'libgen'
MYSQL_TABLE_UPDATE = 'updated'


class MysqlClient:
    def __init__(self):
        self.pool = PooledDB(
            creator=pymysql, maxconnections=50, blocking=True, maxusage=None, ping=4, host=MYSQL_HOST, port=3306, user=MYSQL_USER,
            password=MYSQL_PW, database=MYSQL_DB, autocommit=0
        )

    def create_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def fetch_all(self, sql, args=None):
        try:
            conn, cursor = self.create_conn_cursor()
            cursor.execute(sql, args)
            res = cursor.fetchall()
            cursor.close()
            conn.close()
            return res
        except Exception as e:
            logger.exception(f'查询失败，{e}')
            return False

    def insert_one(self, sql, args=None):
        try:
            conn, cursor = self.create_conn_cursor()
            res = cursor.execute(sql, args)
            conn.commit()
            cursor.close()
            conn.close()
            return res
        except IntegrityError as e:
            logger.error(f'唯一索引插入冲突，{e}')
            return False
        except Exception as e:
            logger.exception(f'插入单条失败，{e}')
            return False

    def insert_batch(self, sql, args=None):
        try:
            conn, cursor = self.create_conn_cursor()
            res = cursor.executemany(sql, args)
            conn.commit()
            cursor.close()
            conn.close()
            return res
        except IntegrityError as e:
            logger.error(f'唯一索引插入冲突，{e}')
            return False
        except Exception as e:
            logger.exception(f'插入多条失败，{e}')
            return False

    def update(self, sql, args=None):
        try:
            conn, cursor = self.create_conn_cursor()
            res = cursor.execute(sql, args)
            conn.commit()
            cursor.close()
            conn.close()
            return res
        except Exception as e:
            logger.exception(f'更新数据失败，{e}')
            logger.error(f'更新失败sql：{sql}')
            return False

    def delete(self, sql, args=None):
        try:
            conn, cursor = self.create_conn_cursor()
            res = cursor.execute(sql, args)
            conn.commit()
            cursor.close()
            conn.close()
            return res
        except Exception as e:
            logger.exception(f'删除失败，{e}')
            return False


mysql_client = MysqlClient()
if __name__ == '__main__':
    # logger.info(mysql_client.fetch_all(f"SELECT ID FROM `updated` WHERE ID IN ('3526467', '3526468');"))
    # logger.info(D:\PythonProgram\catelog_spider\databases\mysql_client.py.insert_one('select * from task Limit 1;'))
    pass
